from mysql import connector

from core.util import CodeUtil

staticConnectors = dict()


"""获取连接"""
def getConnector(user:str='root', password:str='root', host:str='127.0.0.1', database:str='test'):
    cnxCode = CodeUtil.Md5(host + database)
    if staticConnectors.get(cnxCode) is not None:
        cnx = staticConnectors.get(cnxCode)
        #连接一旦关闭， 重开连接
        if cnx.is_connected() is False:
            staticConnectors.pop(cnxCode)
            return getConnector(user=user, password=password, host=host, database=database)
        else:
            return cnx
    else:
        cnx = __Connect(user=user, password=password, host=host, database=database)
        staticConnectors[cnxCode] = cnx
        return cnx


"""数据库连接"""
def __Connect(user:str='root', password:str='root', host:str='127.0.0.1', database:str='test'):
    cnx = connector.connect(user=user, password=password, host=host, database=database)
    return cnx


"""执行一般数据库操作语句"""
def ExcuteSQL(cnx: connector.MySQLConnection, sql: str, params: tuple):
    cursor = cnx.cursor(buffered=True)
    cursor.execute(sql, params)
    chgNum = cursor.rowcount
    cnx.commit()

    cursor.close()
    return chgNum


def QuerySQL(cnx: connector.MySQLConnection, sql, params: tuple):
    cursor = cnx.cursor(buffered=True)
    cursor.execute(sql, params)

    retRows = []
    for line in cursor:
        retRows.append(line)

    cursor.close()
    return retRows

"""执行其他数据库结构操作语句"""
def ExcuteDMLSQL(cnx: connector.MySQLConnection, sql:str):
    cursor = cnx.cursor()
    cursor.execute(sql)
    cnx.commit()
    cursor.close()

"""删表操作"""
def DropTable(cnx: connector.MySQLConnection, tabName: str):
    sql = " TRUNCATE table {} ".format(tabName)
    cursor = cnx.cursor()
    cursor.execute(sql)
    cnx.commit()
    cursor.close()

"""建表操作"""
def CreateTable(cnx: connector.MySQLConnection, sql: str):
    cursor = cnx.cursor()
    cursor.execute(sql)
    cnx.commit()
    cursor.close()

"""关闭连接"""
def CloseConn(cnx: connector.MySQLConnection):
    cnx.close()


"""关闭所有连接"""
def CloseAll():
    for sc in staticConnectors.keys():
        cnx = staticConnectors.get(sc)
        cnx.close()






